﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;

namespace KnowledgeAssistant
{
    class TestUtil
    {
        public static Test GetTest(int test_id)
        {
            Test test = null;

            DataTable teststable = new DataTable();
            teststable = DatabaseConnect.execSP("GetTest", new List<string>() { test_id.ToString() }).Tables[0];

            Theme t = null;

            if (teststable.Rows.Count != 0)
            {
                t = ThemeUtil.GetTheme((int)teststable.Rows[0]["theme_id"]);
                test = new Test((int)teststable.Rows[0]["test_id"], t, teststable.Rows[0]["name"].ToString(), (int)teststable.Rows[0]["test_time"]);
            }

            return test;
        }

        public static List<Test> GetAllTests(int theme_id)
        {
            List<Test> tests = new List<Test>() { };

            DataTable teststable = new DataTable();
            List<string> parameters = new List<string>() { theme_id.ToString() };
            teststable = DatabaseConnect.execSP("GetAllTests", parameters).Tables[0];

            foreach (DataRow r in teststable.Rows)
            {
                tests.Add(GetTest((int)r["test_id"]));
            }

            return tests;
        }

        public static void CreateTest(Test t, List<Question> first_questions, List<List<Answer>> first_answers, List<Question> second_questions, List<List<Answer>> second_answers)
        {
            int test_id = 0;
            int question_id = 0;
            int answer_id = 0;

            List<string> parameters = new List<string>() { };
            parameters.Add(t.Theme.ThemeID.ToString());
            parameters.Add(t.Name);
            parameters.Add(t.Time.ToString());

            test_id = int.Parse(DatabaseConnect.execSP("CreateTest", parameters).Tables[0].Rows[0][0].ToString());
            t.TestID = test_id;

            for (int i = 0; i < first_questions.Count; i++)
            {
                parameters.Clear();
                parameters.Add(test_id.ToString());
                parameters.Add(first_questions[i].Description);
                parameters.Add(first_questions[i].Type.ToString());
                parameters.Add(first_questions[i].Level.LevelID.ToString());

                if (first_questions[i].Image == null)
                {
                    question_id = int.Parse(DatabaseConnect.execSP("CreateQuestion", parameters).Tables[0].Rows[0][0].ToString());
                }
                else
                {
                    question_id = int.Parse(DatabaseConnect.execSP("CreateQuestion", parameters, first_questions[i].Image).Tables[0].Rows[0][0].ToString());
                }
                first_questions[i].QuestionID = question_id;
                // first_questions[i].Test = t;

                for (int j = 0; j < first_answers[i].Count; j++)
                {
                    parameters.Clear();
                    parameters.Add(question_id.ToString());
                    parameters.Add(first_answers[i][j].Description);
                    parameters.Add((first_answers[i][j].True) ? "1" : "0");

                    answer_id = int.Parse(DatabaseConnect.execSP("CreateAnswer", parameters).Tables[0].Rows[0][0].ToString());
                    first_answers[i][j].AnswerID = answer_id;
                    first_answers[i][j].Question = first_questions[i];
                }
            }

            for (int i = 0; i < second_questions.Count; i++)
            {
                parameters.Clear();
                parameters.Add(test_id.ToString());
                parameters.Add(second_questions[i].Description);
                parameters.Add(second_questions[i].Type.ToString());
                parameters.Add(second_questions[i].Level.LevelID.ToString());

                if (first_questions[i].Image == null)
                {
                    question_id = int.Parse(DatabaseConnect.execSP("CreateQuestion", parameters).Tables[0].Rows[0][0].ToString());
                }
                else
                {
                    question_id = int.Parse(DatabaseConnect.execSP("CreateQuestion", parameters, second_questions[i].Image).Tables[0].Rows[0][0].ToString());
                }
                second_questions[i].QuestionID = question_id;
                // second_questions[i].Test = t;

                for (int j = 0; j < second_answers[i].Count; j++)
                {
                    parameters.Clear();
                    parameters.Add(question_id.ToString());
                    parameters.Add(second_answers[i][j].Description);
                    parameters.Add((second_answers[i][j].True) ? "1" : "0");

                    answer_id = int.Parse(DatabaseConnect.execSP("CreateAnswer", parameters).Tables[0].Rows[0][0].ToString());
                    second_answers[i][j].AnswerID = answer_id;
                    second_answers[i][j].Question = second_questions[i];
                }
            }

        }

        public static List<Question> SearchQuestionByDescription(int theme_id, string desc)
        {
            List<Question> questions = new List<Question>() { };

            DataTable fq = new DataTable();
            List<string> parameters = new List<string>() { };
            parameters.Add(theme_id.ToString());
            parameters.Add(desc);

            fq = DatabaseConnect.execSP("QuestSearch", parameters).Tables[0];

            Question q = null;
            foreach (DataRow r in fq.Rows)
            {
                Level level = QuestionUtil.GetLevel(int.Parse(r["level_id"].ToString()));
                q = new Question((int)r["question_id"], 
                    ThemeUtil.GetTheme(theme_id), 
                    r["description"].ToString(), 
                    (byte)r["question_type"], level);

                if ((r["question_image"]).ToString() != "")
                {
                    string s = (r["question_image"]).ToString();
                    byte[] img_bytes = (byte[])r["question_image"];
                    MemoryStream ms = new MemoryStream(img_bytes);
                    q.Image = new Bitmap(ms);
                }
                questions.Add(q);
            }

            return questions;
        }

        public static List<Question> GetThemeQuestionsByType(Theme theme, byte question_type)
        {
            List<Question> questions = new List<Question>() { };

            DataTable fq = new DataTable();
            List<string> parameters = new List<string>() { };
            parameters.Add(theme.ThemeID.ToString());
            parameters.Add(question_type.ToString());

            fq = DatabaseConnect.execSP("GetTestQuestionsByType", parameters).Tables[0];

            Question q = null;
            foreach (DataRow r in fq.Rows)
            {
                Level level = QuestionUtil.GetLevel(int.Parse(r["level_id"].ToString()));
                q = new Question((int)r["question_id"], theme, r["description"].ToString(), (byte)r["question_type"], level);
                if ((r["question_image"]).ToString() != "")
                {
                    string s = (r["question_image"]).ToString();
                    byte[] img_bytes = (byte[])r["question_image"];
                    MemoryStream ms = new MemoryStream(img_bytes);
                    q.Image = new Bitmap(ms);
                }
                questions.Add(q);
            }

            return questions;
        }

        public static void UpdateTestQuestions(Theme theme, List<Question> first_questions,
            List<List<Answer>> first_answers, List<Question> second_questions,
            List<List<Answer>> second_answers, List<Answer> answersToDelete,
            int three, int four, int five)
        {
            ThemeUtil.UpdateTheme(theme.ThemeID, theme.Name, theme.Subject, theme.CountOfFirstQuestions, theme.CountOfSecondQuestionsType1,
                   theme.CountOfSecondQuestionsType2, theme.Duration, three, four, five);

            List<Question> questions = new List<Question>();
            questions.AddRange(GetThemeQuestionsByType(theme, 1));
            questions.AddRange(GetThemeQuestionsByType(theme, 2));
            questions.AddRange(GetThemeQuestionsByType(theme, 3));

            for (int i = 0; i < questions.Count; i++)
            {
                if (first_questions.Find(o => o.QuestionID == questions[i].QuestionID) == null
                    && second_questions.Find(o => o.QuestionID == questions[i].QuestionID) == null)
                {
                    DeleteQuestion(questions[i]);
                }
            }

            for (int i = 0; i < first_questions.Count; i++)
            {
                if (first_questions[i].QuestionID == 0)
                {
                    first_questions[i].QuestionID = QuestionUtil.AddQuestion(first_questions[i]);
                    for (int j = 0; j < first_answers[i].Count; j++)
                    {
                        first_answers[i][j].AnswerID = AnswerUtil.AddAnswer(first_answers[i][j]);
                    }
                }
                else
                {
                    QuestionUtil.UpdateQuestion(first_questions[i]);
                    for (int j = 0; j < first_answers[i].Count; j++)
                    {
                        AnswerUtil.UpdateAnswer(first_answers[i][j]);
                    }
                }
            }

            for (int i = 0; i < second_questions.Count; i++)
            {
                if (second_questions[i].QuestionID == 0)
                {
                    second_questions[i].QuestionID = QuestionUtil.AddQuestion(second_questions[i]);
                    for (int j = 0; j < second_answers[i].Count; j++)
                    {
                        second_answers[i][j].Question = second_questions[i];
                        second_answers[i][j].AnswerID = AnswerUtil.AddAnswer(second_answers[i][j]);
                    }
                }
                else
                {
                    QuestionUtil.UpdateQuestion(second_questions[i]);
                    for (int j = 0; j < second_answers[i].Count; j++)
                    {
                        second_answers[i][j].Question = second_questions[i];
                        if (second_answers[i][j].AnswerID != 0)
                        {
                            AnswerUtil.UpdateAnswer(second_answers[i][j]);
                        }
                        else
                        {
                            AnswerUtil.AddAnswer(second_answers[i][j]);
                        }
                    }
                }
            }

            if (answersToDelete.Count != 0)
            {
                foreach (Answer a in answersToDelete)
                {
                    AnswerUtil.DeleteAnswer(a);
                }
            }

        }

        public static List<Answer> GetQuestionAnswersByQuestion(Question question)
        {
            List<Answer> answers = new List<Answer>() { };

            DataTable aq = new DataTable();
            List<string> parameters = new List<string>() { };
            parameters.Add(question.QuestionID.ToString());

            aq = DatabaseConnect.execSP("GetAnswersByQuestion", parameters).Tables[0];

            Answer a = null;
            foreach (DataRow r in aq.Rows)
            {
                a = new Answer((int)r["answer_id"], r["description"].ToString(), question, (bool)r["true"]);
                answers.Add(a);
            }

            return answers;
        }

        public static void DeleteTest(int test_id)
        {
            List<string> parameters = new List<string>() { };
            parameters.Add(test_id.ToString());

            DatabaseConnect.execSPNonQuery("DeleteTest", parameters);
        }

        public static int CreateTestResults(Theme t, List<Question> questions, List<List<Answer>> answers,
            int student_id, int duration_time, DateTime date_begin)
        {
            List<string> parameters = new List<string>() { };
            parameters.Add(t.ThemeID.ToString());
            parameters.Add(student_id.ToString());
            parameters.Add(duration_time.ToString());
            parameters.Add(date_begin.Day + "/" + date_begin.Month + "/" + date_begin.Year + " " + date_begin.Hour + ":" + date_begin.Minute + ":" + date_begin.Second);

            int archive_test_id = int.Parse(DatabaseConnect.execSP("CreateArchiveTest", parameters).Tables[0].Rows[0][0].ToString());

            for (int i = 0; i < questions.Count; i++)
            {
                parameters.Clear();
                parameters.Add(archive_test_id.ToString());
                parameters.Add(questions[i].QuestionID.ToString());
                parameters.Add(questions[i].Answered == null || !questions[i].Answered ? "0" : "1");

                int archive_question_id = int.Parse(DatabaseConnect.execSP("CreateArchiveQuestion", parameters).Tables[0].Rows[0][0].ToString());

                for (int j = 0; j < answers[i].Count; j++)
                {
                    parameters.Clear();
                    parameters.Add(archive_question_id.ToString());
                    parameters.Add(answers[i][j].AnswerID.ToString());
                    parameters.Add(answers[i][j].Checked == null || !answers[i][j].Checked ? "0" : "1");

                    int archive_answer_id = int.Parse(DatabaseConnect.execSP("CreateArchiveAnswer", parameters).Tables[0].Rows[0][0].ToString());
                }
            }

            return archive_test_id;
        }

        public static DataSet GetTestResultsInfo(int archive_test_id)
        {
            DataSet tables = new DataSet();
            tables = DatabaseConnect.execSP("GetTestResultsInfo", new List<string>() { archive_test_id.ToString() });
            return tables;
        }

        public static void DeleteQuestion(Question q)
        {
            List<string> parameters = new List<string>() { };
            parameters.Add(q.QuestionID.ToString());
            DatabaseConnect.execSP("DeleteQuestion", parameters);
        }

        public static void DeleteArchiveTest(int archive_test_id)
        {
            List<string> parameters = new List<string>() { };
            parameters.Add(archive_test_id.ToString());
            DatabaseConnect.execSP("DeleteArchiveTest", parameters);
        }
    }
}
